package com.reverse.engineering.util.core.run.util.interfaces.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.ApplicationProperties;
import com.reverse.engineering.util.core.run.util.StringUtils;
import com.reverse.engineering.util.core.run.util.interfaces.DatabaseUtilsInterface;

/**
 * Database工具类实现类
 * 
 * @author liu
 * @Date 2020-06-15 14:45:59
 * @Description
 *
 */
public class DatabaseUtilsImpl implements DatabaseUtilsInterface {

    private static DatabaseUtilsImpl dbUtilsImpl;

    private DatabaseUtilsImpl() {}

    /**
     * 执行Sql插入语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int insert(String sql, Object obj[]) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            // 预编译sql,Statement.RETURN_GENERATED_KEYS返回主键
            prepareStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(null, prepareStatement, conn);
        }
        return count;
    }

    /**
     * 执行Sql插入语句(长连接)
     * 
     * @param conn
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int insert(Object conn, String sql, Object obj[]) throws SQLException {
        Connection connection = (Connection)conn;
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            // 预编译sql,Statement.RETURN_GENERATED_KEYS返回主键
            prepareStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(null, prepareStatement, null);
        }
        return count;
    }

    /**
     * 执行Sql更新语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int executeUpdate(String sql, Object obj[]) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            // 预编译sql
            prepareStatement = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(null, prepareStatement, conn);
        }
        return count;
    }

    /**
     * 执行Sql更新语句(长连接)
     * 
     * @param conn
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public int executeUpdate(Object conn, String sql, Object obj[]) throws SQLException {
        Connection connection = (Connection)conn;
        PreparedStatement prepareStatement = null;
        int count = 0;
        try {
            // 预编译sql
            prepareStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            count = prepareStatement.executeUpdate();
        } finally {
            close(null, prepareStatement, null);
        }
        return count;
    }

    /**
     * 执行Sql查询语句
     * 
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public List<HashMap<String, Object>> executeQuery(String sql, Object obj[]) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement prepareStatement = null;
        ResultSet resu = null;
        List<HashMap<String, Object>> resultToListMap = null;
        try {
            // 预编译sql
            prepareStatement = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            resu = prepareStatement.executeQuery();
            resultToListMap = resultToListMap(resu);
        } finally {
            close(resu, prepareStatement, conn);
        }
        return resultToListMap;
    }

    /**
     * 执行Sql查询语句(长连接)
     * 
     * @param conn
     * @param sql Sql语句
     * @param obj 参数
     * @return
     * @throws SQLException
     */
    @Override
    public List<HashMap<String, Object>> executeQuery(Object conn, String sql, Object obj[]) throws SQLException {
        Connection connection = (Connection)conn;
        PreparedStatement prepareStatement = null;
        ResultSet resu = null;
        List<HashMap<String, Object>> resultToListMap = null;
        try {
            // 预编译sql
            prepareStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                // 给sql语句占位符赋值
                prepareStatement.setObject(i + 1, obj[i]);
            }
            resu = prepareStatement.executeQuery();
            resultToListMap = resultToListMap(resu);
        } finally {
            close(resu, prepareStatement, null);
        }
        return resultToListMap;
    }

    /**
     * 获取返回结果
     * 
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<HashMap<String, Object>> resultToListMap(ResultSet rs) throws SQLException {
        List<HashMap<String, Object>> list = new ArrayList<>();
        while (rs.next()) {
            ResultSetMetaData md = rs.getMetaData();
            HashMap<String, Object> map = new HashMap<>(30);
            for (int i = 1; i < md.getColumnCount(); i++) {
                map.put(md.getColumnLabel(i), rs.getObject(i));
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 获取连接对象
     * 
     * @return
     * @throws SQLException
     */
    @Override
    public Connection getConnection() throws SQLException {
        return getConnection(
            ApplicationProperties.URL + ApplicationProperties.DATABASE_NAME + ApplicationProperties.SSL,
            ApplicationProperties.USERNAME, ApplicationProperties.PASSWORD);
    }

    /**
     * 获取连接对象
     * 
     * @param url 数据库路径
     * @param username 数据库帐号
     * @param password 数据库密码
     * @return
     * @throws SQLException
     */
    @Override
    public Connection getConnection(String url, String username, String password) throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 获取连接对象
     * 
     * @param url 数据库路径
     * @param port 端口号
     * @param username 数据库帐号
     * @param password 数据库密码
     * @return
     * @throws SQLException
     */
    @Override
    public Connection getConnection(String url, int port, String username, String password) throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 关闭连接(单个连接)
     * 
     * @param conn 要关闭的Connection对象
     * @throws SQLException
     */
    public void closeOne(Connection conn) throws SQLException {
        close(null, null, conn);
    }

    /**
     * 关闭连接(一组连接)
     * 
     * @param resu 要关闭的ResultSet对象
     * @param stat 要关闭的Statement对象
     * @param conn 要关闭的Connection对象
     * @throws SQLException
     */
    public void close(ResultSet resu, Statement stat, Connection conn) throws SQLException {
        if (StringUtils.isNotBlankObject(resu)) {
            resu.close();
        }
        if (StringUtils.isNotBlankObject(stat)) {
            stat.close();
        }
        if (StringUtils.isNotBlankObject(conn)) {
            conn.close();
        }
    }

    /**
     * 关闭连接(多组连接)
     * 
     * @param resu 要关闭的ResultSet对象集合
     * @param stat 要关闭的Statement对象集合
     * @param conn 要关闭的Connection对象集合
     * @throws SQLException
     */
    public void closeList(ArrayList<ResultSet> resu, ArrayList<Statement> stat, Connection conn) throws SQLException {
        if (StringUtils.isNotBlankObject(resu)) {
            for (ResultSet resultSet : resu) {
                if (StringUtils.isNotBlankObject(resultSet)) {
                    resultSet.close();
                }
            }
        }
        if (StringUtils.isNotBlankObject(stat)) {
            for (Statement statement : stat) {
                if (StringUtils.isNotBlankObject(statement)) {
                    statement.close();
                }
            }
        }
        if (StringUtils.isNotBlankObject(conn)) {
            conn.close();
        }
    }

    /**
     * 关闭连接(单个连接,顶级连接,比如:一级连接)
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException 
     */
    @Override
    public void utilsCloseOne(Object conn) throws SQLException {
        closeOne((Connection)conn);
    }

    /**
     * 关闭连接(一组连接,比如:三级连接,二级连接,一级连接...)
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException
     */
    @Override
    public void utilsClose(Object... conn) throws SQLException {
        close((ResultSet)conn[0], (Statement)conn[1], (Connection)conn[2]);
    }

    /**
     * 关闭连接(多组连接,每组除顶级外都是List,顶级是一个Object,比如:{三级连接...},{二级连接...},顶级连接)
     * 
     * @param conn 要关闭的对象集合
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    @Override
    public void utilsCloseList(Object... conn) throws SQLException {
        closeList((ArrayList<ResultSet>)conn[0], (ArrayList<Statement>)conn[1], (Connection)conn[2]);
    }

    public static DatabaseUtilsImpl getInstances() {
        if (StringUtils.isBlankObject(dbUtilsImpl)) {
            dbUtilsImpl = new DatabaseUtilsImpl();
        }
        return dbUtilsImpl;
    }

}